﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetDistribution_SelectForGrid')
	DROP PROCEDURE up_UtilityAssetDistribution_SelectForGrid
GO

CREATE PROCEDURE up_UtilityAssetDistribution_SelectForGrid
	 @UtilityFk int 
	,@PageSize int = 100
	,@PageNumber int = 1
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
AS

SET NOCOUNT ON	

DECLARE @StartRowNumber int
DECLARE @EndRowNumber int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize

DECLARE @table table
(
 	 RowNumber int
	,TotalCount int
	,Utility_Asset_DistributionId int
	,UtilityFK int
	,Latitude float
	,Longitude float
	,Altitude float
	,SatelliteCount int
	,SatelliteTime datetime
	,SolutionAvailable bit
	,Handheld nvarchar(100)
	,Distribution_TopTypeFk int
	,Utility_ContactFK int
	,[Date] datetime
	,Utility_ProjectFK int
	,MapFk int
	,SectionFk int
	,[FriendlyName] varchar(255)
	,TopTypeDisplay varchar(MAX)
	,PhaseDisplay varchar(MAX)
	,[Year] int	
	,TreatmentFk int
	,ClassFk int
	,SpeciesFk int
	,Length int
	,LastVisit varchar(MAX)
	,Number varchar(255)
	,ParentAssetFk1 int 
	,ParentAssetFk2 int
	,ParentAssetFk3 int
	,ParentAssetFk4 int
	,ParentAssetFk5 int
)
	
INSERT @table
(
	 [RowNumber]
	,[TotalCount]
	,[Utility_Asset_DistributionId]
	,[UtilityFK]
	,[Latitude]
	,[Longitude]
	,[Altitude]
	,[SatelliteCount]
	,[SatelliteTime]
	,[SolutionAvailable]
	,[Handheld]
	,[Distribution_TopTypeFk]
	,[Utility_ContactFK]
	,[Date]
	,[Utility_ProjectFK]
	,MapFk
	,SectionFk	
	,[FriendlyName]
	,TopTypeDisplay
	,PhaseDisplay
	,[Year]
	,TreatmentFk
	,ClassFk
	,SpeciesFk
	,Length
	,LastVisit
	,Number
	,ParentAssetFk1
	,ParentAssetFk2
	,ParentAssetFk3
	,ParentAssetFk4
	,ParentAssetFk5
)
SELECT 
*
FROM 
(
	SELECT 
		 ROW_NUMBER() OVER (ORDER BY Utility_Asset_DistributionId) AS RowNumber
		,COUNT(Utility_Asset_DistributionId) OVER (PARTITION BY NULL) AS TotalCount
		,[Utility_Asset_DistributionId]
		,[UtilityFK]
		,[Latitude]
		,[Longitude]
		,[Altitude]
		,[SatelliteCount]
		,[SatelliteTime]
		,[SolutionAvailable]
		,[Handheld]
		,[Distribution_TopTypeFk]
		,[Utility_ContactFK]
		,[Date]
		,[Utility_ProjectFK]
		,MapFk
		,SectionFk
		,[FriendlyName]
		,dbo.fn_DistributionPole_GetTopTypeString(Utility_Asset_DistributionId, 1) AS TopTypeDisplay
		,dbo.fn_DistributionPole_GetPhaseString(Utility_Asset_DistributionId, 1) AS PhaseDisplay
		,[Year]
		,TreatmentFk
		,ClassFk
		,SpeciesFk
		,Length
		,(SELECT TOP 1
			ISNULL(vendors.Name, '')+ ' ' + CAST(YEAR(ISNULL(poles.AcquisitionDate, '')) AS NVARCHAR)
			FROM 
				tb_Vendor_Distribution_Poles poles
			INNER JOIN tb_Vendors vendors
				ON vendors.VendorId = poles.VendorFk
			WHERE 
				poles.Utility_Asset_Distributions_PoleFk = v_tb_Utility_Asset_Distributions.Utility_Asset_DistributionId
			ORDER BY
				poles.acquisitiondate DESC) LastVisit
		,Number
		,ParentAssetFk1
		,ParentAssetFk2
		,ParentAssetFk3
		,ParentAssetFk4
		,ParentAssetFk5
	FROM 
		v_tb_Utility_Asset_Distributions
	WHERE
		[UtilityFK] = @UtilityFk
) AS Data
WHERE RowNumber BETWEEN @StartRowNumber AND @EndRowNumber

-- primary select
SELECT * FROM @table



